package com.ttl.bc.ap.invoice.bo;

import hibernate.util.HibernateUtil;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.ActionForm;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

import com.ttl.bc.ap.ap.bean.BCAPBean;
import com.ttl.bc.ap.ap.db.BCAPDAO;


import com.ttl.bc.ap.invoice.action.BCAPInvoice2Form;
import com.ttl.bc.ap.invoice.bean.BCAPInvoiceBean;
import com.ttl.bc.ap.invoice.bean.BCAPInvoiceSubBean;
import com.ttl.bc.ap.invoice.bean.ITTL_P2;
import com.ttl.bc.ap.paymoney.bean.BCPayMoneyBean;
import com.ttl.bc.ap.paymoney.db.BCPayMoneyBO;
import com.ttl.bc.ap.paymoney.db.BCPayMoneyDAO;

import com.ttl.bc.ap.purchaseorder.bean.BCPurchaseOrderBean;
import com.ttl.bc.ap.purchaseorder.db.BCPurchaseOrderDAO;
import com.ttl.bc.ap.stkrequest.BCStkRequestBean;
import com.ttl.bc.ap.stkrequest.BCStkRequestDAO;
import com.ttl.bc.ap.stkrequest.BCStkRequestSubBean;
import com.ttl.bc.ap.stkrequest.BCStkRequestSubShowBean;


import com.ttl.bc.gl.trans.bean.BCTrans;
import com.ttl.bc.gl.trans.bean.BCTransSub;
import com.ttl.bc.master.itemunit.BCItemUnitBean;
import com.ttl.bc.processstock.bean.ProcessStockBean;
import com.ttl.bc.processstock.db.ProcessStockDAO;

import com.ttl.bc.vat.apwtaxlist.bean.BCAPWTaxListBean;
import com.ttl.bc.vat.apwtaxlist.db.BCAPWTaxListBO;
import com.ttl.bc.vat.apwtaxlist.db.BCAPWTaxListDAO;

import com.ttl.bc.vat.inputtax.bean.BCInputTaxBean;
import com.ttl.bc.vat.inputtax.db.BCInputTaxBO;
import com.ttl.bc.vat.inputtax.db.BCInputTaxDAO;


import com.ttl.common.db.ADAO;
import com.ttl.common.util.CommonUtils;

public class BCAPInvoiceDAO extends ADAO {
	@SuppressWarnings("unchecked")
	@Override
	public boolean update(String s, Object o) {
		Session session = null;

		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			 session = sessionFactory.openSession();
			session.beginTransaction();

            String hql = "FROM BCAPInvoiceBean h where h.docNo = '"+s+"'";
            org.hibernate.Query q = session.createQuery(hql);
            List resultList = q.list();			

            for(int i=0; i<resultList.size(); i++){
            	BCAPInvoiceBean h = (BCAPInvoiceBean)resultList.get(i);                        
                session.delete(h);
            }            
            
			session.save(o);
			session.getTransaction().commit();
			return true;
        } catch (HibernateException he) {
            he.printStackTrace();
			return false;
		}finally {
			session.close();
		}
	}

	@SuppressWarnings("unchecked")
	public boolean addITTL_P2(BCAPInvoiceBean inv) {
		Session session = null;

		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			 session = sessionFactory.openSession();
			session.beginTransaction();
			
			String hql = "FROM BCPurchaseOrderBean WHERE docNo in (";
			Iterator it = inv.getBcAPInvoiceSubs().iterator();
			while (it.hasNext()) {
				BCAPInvoiceSubBean isub = (BCAPInvoiceSubBean) it.next();
				hql +="'"+isub.getPoRefNo()+"',";
			}
			hql = hql.substring(0, hql.length()-1)+") order by docNo";
			
            org.hibernate.Query q = session.createQuery(hql);
            List poList = q.list();				
			
			ArrayList al = new BCAPInvoiceBO().getP2List(inv);
			al = new BCAPInvoiceBO().getP2SetPODetails(poList, al);
			al = new BCAPInvoiceBO().getP2ProjectCode(inv, al);
			
			hql = "FROM BCAPBean WHERE code = '"+inv.getApCode()+"'";
			q = session.createQuery(hql);
			
			if(q.list().size()>0) {
				BCAPBean ap = (BCAPBean)q.list().get(0);
				for (int i = 0; i < al.size(); i++) {
					ITTL_P2 p2 = (ITTL_P2)al.get(i);
					p2.setApName(ap.getName1());
				}
			}
			
			hql = "FROM BCItemUnitBean ";
			q = session.createQuery(hql);
			
			if(q.list().size()>0) {
				for (int i = 0; i < al.size(); i++) {
					ITTL_P2 p2 = (ITTL_P2)al.get(i);
					for (int u = 0; u < q.list().size(); u++) {
						BCItemUnitBean unit = (BCItemUnitBean)(q.list().get(u));
						if(p2.getUnitCode().equalsIgnoreCase(unit.getCode())) {
							p2.setUnitName(unit.getName());
						}
					}
				}
			}			
			
			
			for (int i = 0; i < al.size(); i++) {
				ITTL_P2 p2 = (ITTL_P2)al.get(i);
				session.save(p2);
			}			
			
			session.getTransaction().commit();
			
			return true;
		}catch(Exception e) {
			System.out.println(e.toString());
			return false;
		}finally {
			session.close();
		}
	}

	@SuppressWarnings("unchecked")
	public boolean summaryInv(String[] docNoArray, String[] typeArray) {
		
		String docNo = "";
		for(int i=0; i<docNoArray.length; i++) {
			docNo = docNo +"'"+ docNoArray[i].trim() + "',";
		}
		if(docNo.length()>0) {
			docNo = docNo.substring(0, docNo.length()-1);
		}
		
		String hql = "FROM BCAPInvoiceBean   " +
		 "WHERE  " +
		 " docNo in ( "+docNo+" )  ";		
		
		Session session = null;

		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			 session = sessionFactory.openSession();
			session.beginTransaction();

            org.hibernate.Query q = session.createQuery(hql);
            List resultList = q.list();			

            for(int i=0; i<resultList.size(); i++){
            	BCAPInvoiceBean h = (BCAPInvoiceBean)resultList.get(i);
            	
            	for (int j = 0; j < docNoArray.length; j++) {
                	if(h.getDocNo().equalsIgnoreCase(docNoArray[j])) {
                		h.setRemark1("KIND OF METERIALS");
                		h.setRemark2(typeArray[j]);
                		session.save(h);
                	}                    		
				}
            }            
            
			session.getTransaction().commit();
			return true;
        } catch (HibernateException he) {
            he.printStackTrace();
			return false;
		}finally {
			session.close();
		}
	}
	
	@SuppressWarnings("unchecked")
	public boolean deleteInvoice(String docNo) {
		
		String[] item_of_service = {"A-01", "A-02", "C-3",  "D-03", "D-04", "D-4",  "E-04", "E-05", "E-4",  "E-5",  "E-S-E",
				"F-04", "F-05", "F-06", "F-4",  "F-5",  "F-6",  "G-06", "G-08", "G-10", "G-11", "G-12", 
				"G-13", "G-14", "G-24", "GE",   "H-07", "H-08", "H-15", "HO",   "I-10", "I-16", "IM",
				"INT",  "J-11", "J-17", "K-14", "K-15", "K-16", "K-17", "K-18", "L-18", "L-19", "M-19",
				"M-21", "N-20", "N-22", "O-21", "P-22", "P-23", "Q-23", "R-24", "R-25", "S-25", "SE", "TREEX", "TRECO", 
				"SER-006","SES","ST",   "T-26", "U-27", "V-28", "Z-01", "Z-02", "Z-03", "Z-04", "Z-05", "Z-06"};
		Session session = null;

		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			 session = sessionFactory.openSession();
			session.beginTransaction();
			
            org.hibernate.Query q = session.createQuery("FROM BCAPInvoiceBean WHERE docNo = '"+docNo+"'");
            List resultList = q.list();	
            for(int i=0; i<resultList.size(); i++){
            	BCAPInvoiceBean inv = (BCAPInvoiceBean)resultList.get(i);
            	inv.setIsCancel(1);
            	
            	Iterator it = inv.getBcAPInvoiceSubs().iterator();
            	while (it.hasNext()) {
					BCAPInvoiceSubBean iSub = (BCAPInvoiceSubBean) it.next();
					iSub.setIsCancel(1);
//----------------
					// ใส่ข้อมูล itemCode รายการนั้นใน table: processStock 
//					ProcessStockDAO p = new ProcessStockDAO();
												
//					BCStkRequestDAO dao2 = new BCStkRequestDAO();
					//dao2.updateStatus(sub.getPoRefNo());
					
					//update ว่าใบ พ.1 ขอซื้อนั้น ใช้บันทึกซื้อแล้ว (แม้ไม่ได้ซึ้อทุก item )
//					dao2.updateStatus(iSub.getStkReqNo(),"1"); //0=รอเบิก, 1=รอซื้อสร้าง po แล้ว, 2=ซื้อแล้ว, 3=จ่ายแล้ว, 4=ยืนยันรับของ
					//update item ที่ซื้อเข้ามาตาม rowOrder
//					dao2.updateStatusAfterCreateInvoice(form,"2");
					
//					BCReqConfirmDAO dao3 = new BCReqConfirmDAO();
//					//dao3.updateStatus(sub.getPoRefNo());
//					dao3.updateStatus(sub.getStkReqNo(),"2");
					
//					BCPurchaseOrderDAO dao4 = new BCPurchaseOrderDAO();
//					dao4.updateStatusAfterSaveInvoice(iSub.getPoRefNo());
					
					// หลังจาก delete invoice จะต้อง update : poStatus=0, billStatus=0				
					org.hibernate.Query qpo = session.createQuery("FROM BCPurchaseOrderBean where docNo = '"+iSub.getPoRefNo()+"'");
		            List resultListPO = qpo.list();	
		            for(int ix=0; ix<resultListPO.size(); ix++){
		            	BCPurchaseOrderBean po = (BCPurchaseOrderBean)resultListPO.get(ix);
		            	po.setPoStatus(0);
		            	po.setBillStatus(0);
		            	session.save(po);
			    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :BCPurchaseOrderBean #cancel_used "+
				  				  "#docNo:"+po.getDocNo());		            	
		            }					
					
					boolean isItemService = false;
					for (int ix = 0; ix < item_of_service.length; ix++) {
						if(item_of_service[ix].contains(iSub.getItemCode())) {
							isItemService = true;
						}				
					}
					if(isItemService==false) {
			        	ProcessStockBean bean = new ProcessStockBean();
			        	bean.setItemCode(iSub.getItemCode());
			        	bean.setDocDate(iSub.getDocDate());
			        	bean.setProcessFlag(0);
			        	bean.setFlowStatus(1);
			        	bean.setProcessType(5);
			        	bean.setProcessCase(3);// สำหรับ BCStkIssue บันทึกเอกสารให้ =1, ลบเอกสารให้=3
			        		        	
			        	session.save(bean);
			        	
			    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :ProcessStockBean #delete "+
			  				  "#item:"+iSub.getItemCode());
//			 				BCWinService จะ update: lastBuyPrice ให้กับ item ที่ซื้อเข้ามา แบบ auto อยู่แล้ว	
//				    		new BCItemDAO().updateLastBuyPrice(sub.getItemCode(),sub.getPrice());			    		
					}
					//###########										
//----------------					
				}
            	if(inv.getBillType()!=null && (inv.getBillType()==1 || inv.getBillType()==3 )) {
            		org.hibernate.Query qap = session.createQuery("FROM BCAPBean WHERE code = '"+inv.getApCode()+"'");
	                List resultListAP = qap.list();	
	                for(int ix=0; ix<resultListAP.size(); ix++){
	                	BCAPBean ap = (BCAPBean)resultListAP.get(ix);
	                	ap.setDebtAmount(ap.getDebtAmount()-inv.getTotalAmount());
	                	session.save(ap);
	    	    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :BCAPBean #ลด DebtAmount: "+inv.getTotalAmount()+
	    		  				  "#code:"+inv.getApCode());            	            	            	
	                }
            	}
            	
            	
            	session.save(inv);
            }
            
            q = session.createQuery("FROM BCPayMoneyBean WHERE docNo = '"+docNo+"'");
            resultList = q.list();	
            for(int i=0; i<resultList.size(); i++){
            	BCPayMoneyBean pm = (BCPayMoneyBean)resultList.get(i);
            	session.delete(pm);
	    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :BCPayMoneyBean #delete "+
		  				  "#docNo:"+pm.getDocNo());            	
            }            
			
            q = session.createQuery("FROM BCInputTaxBean WHERE docNo = '"+docNo+"'");
            resultList = q.list();	
            for(int i=0; i<resultList.size(); i++){
            	BCInputTaxBean tax = (BCInputTaxBean)resultList.get(i);
            	tax.setShortTaxDesc("ยกเลิก");
            	tax.setBeforeTaxAmount(0d);
            	tax.setTaxAmount(0d);
            	session.save(tax);
	    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :BCInputTaxBean #cancel "+
		  				  "#docNo:"+tax.getDocNo());            	            	
            }            
            
            q = session.createQuery("FROM BCAPWTaxListBean WHERE docNo = '"+docNo+"'");
            resultList = q.list();	
            for(int i=0; i<resultList.size(); i++){
            	BCAPWTaxListBean wtax = (BCAPWTaxListBean)resultList.get(i);
            	session.delete(wtax);
	    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :BCAPWTaxListBean #delete "+
		  				  "#docNo:"+wtax.getDocNo());            	            	            	
            }
                         
            
            q = session.createQuery("FROM BCTrans WHERE docNo = '"+docNo+"'");
            resultList = q.list();	
            for(int i=0; i<resultList.size(); i++){
            	BCTrans t = (BCTrans)resultList.get(i);
            	t.setMyDescription("ยกเลิก");
            	t.setIsCancel(1);
            	session.save(t);
	    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :BCTrans #cancel "+
		  				  "#docNo:"+t.getDocNo());            	
            }

            q = session.createQuery("FROM BCTransSub WHERE docNo = '"+docNo+"'");
            resultList = q.list();	
            for(int i=0; i<resultList.size(); i++){
            	BCTransSub tsub = (BCTransSub)resultList.get(i);
            	session.delete(tsub);
	    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :BCTransSub #delete "+
		  				  "#docNo:"+tsub.getDocNo());             	
            }
            
            q = session.createQuery("FROM ITTL_P2 WHERE docNo = '"+docNo+"'");
            resultList = q.list();	
            for(int i=0; i<resultList.size(); i++){
            	ITTL_P2 p2 = (ITTL_P2)resultList.get(i);
            	p2.setIsCancel(1);
            	session.save(p2);
	    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :ITTL_P2 #cancel "+
		  				  "#docNo:"+p2.getDocNo());              	
            }
            
			session.getTransaction().commit();						
			
    		System.out.println(CommonUtils.getDateTimeForStamp()+" Database Commit");              				
			return true;
		}catch(Exception e) {
			System.out.println(e.toString());
			System.out.println(CommonUtils.getDateTimeForStamp()+" Database Not Commit");
			return false;
		}finally {
			session.close();
		}
	}

	@SuppressWarnings("unchecked")
	public boolean addPrintCount(String docNoParam) {
		Session session = null;

		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			 session = sessionFactory.openSession();
			session.beginTransaction();

            String hql = "FROM BCAPInvoiceBean i where "+docNoParam;
            org.hibernate.Query q = session.createQuery(hql);
            List results = q.list();			

            if(results!=null) {
	            for(int i=0; i<results.size(); i++){
	            	BCAPInvoiceBean h = (BCAPInvoiceBean)results.get(i);
	            	if(h.getPrintCount()!=null) { 
	            		h.setPrintCount(h.getPrintCount()+1);
	            	}else {
	            		h.setPrintCount(1);
	            	}
	            	session.save(h);
	            }
            }
            
			session.getTransaction().commit();
			
			return true;
        } catch (HibernateException he) {
            he.printStackTrace();        
			return false;
		}finally {
			session.close();
		}
	}

	@SuppressWarnings("unchecked")
	public boolean updateInvoice(ActionForm form, BCAPInvoiceBean invoice) {
		
		String[] item_of_service = {"A-01", "A-02", "C-3",  "D-03", "D-04", "D-4",  "E-04", "E-05", "E-4",  "E-5",  "E-S-E",
				"F-04", "F-05", "F-06", "F-4",  "F-5",  "F-6",  "G-06", "G-08", "G-10", "G-11", "G-12", 
				"G-13", "G-14", "G-24", "GE",   "H-07", "H-08", "H-15", "HO",   "I-10", "I-16", "IM",
				"INT",  "J-11", "J-17", "K-14", "K-15", "K-16", "K-17", "K-18", "L-18", "L-19", "M-19",
				"M-21", "N-20", "N-22", "O-21", "P-22", "P-23", "Q-23", "R-24", "R-25", "S-25", "SE", "TREEX", "TRECO", 
				"SER-006","SES","ST",   "T-26", "U-27", "V-28", "Z-01", "Z-02", "Z-03", "Z-04", "Z-05", "Z-06"};
		Session session = null;

		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			 session = sessionFactory.openSession();
			session.beginTransaction();
			            
			org.hibernate.Query q = session.createQuery("FROM BCPayMoneyBean WHERE docNo = '"+invoice.getDocNo()+"'");
			List resultList = q.list();	
            for(int i=0; i<resultList.size(); i++){
            	BCPayMoneyBean pm = (BCPayMoneyBean)resultList.get(i);
            	session.delete(pm);
	    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :BCPayMoneyBean #delete "+
		  				  "#docNo:"+pm.getDocNo());            	
            }            

            
            q = session.createQuery("FROM BCInputTaxBean WHERE docNo = '"+invoice.getDocNo()+"'");
            resultList = q.list();	
            for(int i=0; i<resultList.size(); i++){
            	BCInputTaxBean tax = (BCInputTaxBean)resultList.get(i);     
            	session.delete(tax);
	    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :BCInputTaxBean #delete "+
		  				  "#docNo:"+tax.getDocNo());            	            	
            }            
            
            q = session.createQuery("FROM BCAPWTaxListBean WHERE docNo = '"+invoice.getDocNo()+"'");
            resultList = q.list();	
            for(int i=0; i<resultList.size(); i++){
            	BCAPWTaxListBean wtax = (BCAPWTaxListBean)resultList.get(i);
            	session.delete(wtax);
	    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :BCAPWTaxListBean #delete "+
		  				  "#docNo:"+wtax.getDocNo());            	            	            	
            }
                         
            
            q = session.createQuery("FROM BCTrans WHERE docNo = '"+invoice.getDocNo()+"'");
            resultList = q.list();	
            for(int i=0; i<resultList.size(); i++){
            	BCTrans t = (BCTrans)resultList.get(i);
            	session.delete(t);
	    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :BCTrans #delete "+
		  				  "#docNo:"+t.getDocNo());            	
            }

            q = session.createQuery("FROM BCTransSub WHERE docNo = '"+invoice.getDocNo()+"'");
            resultList = q.list();	
            for(int i=0; i<resultList.size(); i++){
            	BCTransSub tsub = (BCTransSub)resultList.get(i);
            	session.delete(tsub);
	    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :BCTransSub #delete "+
		  				  "#docNo:"+tsub.getDocNo());             	
            }
            
            q = session.createQuery("FROM ITTL_P2 WHERE docNo = '"+invoice.getDocNo()+"'");
            resultList = q.list();	
            for(int i=0; i<resultList.size(); i++){
            	ITTL_P2 p2 = (ITTL_P2)resultList.get(i);            	
            	session.delete(p2);
	    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :ITTL_P2 #delete "+
		  				  "#docNo:"+p2.getDocNo());              	
            }

            q = session.createQuery("FROM BCAPInvoiceBean WHERE docNo = '"+invoice.getDocNo()+"'");
            resultList = q.list();	
            for(int i=0; i<resultList.size(); i++){
            	BCAPInvoiceBean inv = (BCAPInvoiceBean)resultList.get(i);
            	
            	invoice.setCreatorCode(inv.getCreatorCode());
            	invoice.setCreateDateTime(inv.getCreateDateTime());
            	
            	session.delete(inv);
            	
	    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :BCAPInvoiceBean #delete "+
		  				  "#docNo:"+inv.getDocNo());
	    		
            	if(inv.getBillType()!=null && (inv.getBillType()==1 || inv.getBillType()==3 )) {
            		org.hibernate.Query qap = session.createQuery("FROM BCAPBean WHERE code = '"+inv.getApCode()+"'");
	                List resultListAP = qap.list();	
	                for(int ix=0; ix<resultListAP.size(); ix++){
	                	BCAPBean ap = (BCAPBean)resultListAP.get(ix);
	                	ap.setDebtAmount(ap.getDebtAmount()-inv.getTotalAmount());
	                	session.save(ap);
	    	    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :BCAPBean #ลด DebtAmount: "+inv.getTotalAmount()+
	    		  				  "#code:"+inv.getApCode());            	            	            	
	                }
            	}
            }            	
            	
            Iterator it = invoice.getBcAPInvoiceSubs().iterator();
        	while (it.hasNext()) {
				BCAPInvoiceSubBean iSub = (BCAPInvoiceSubBean) it.next();					
				
				boolean isItemService = false;
				for (int ix = 0; ix < item_of_service.length; ix++) {
					if(item_of_service[ix].contains(iSub.getItemCode())) {
						isItemService = true;
					}				
				}
				if(isItemService==false) {
		        	ProcessStockBean bean = new ProcessStockBean();
		        	bean.setItemCode(iSub.getItemCode());
		        	bean.setDocDate(iSub.getDocDate());
		        	bean.setProcessFlag(0);
		        	bean.setFlowStatus(1);
		        	bean.setProcessType(5);
		        	bean.setProcessCase(2);// สำหรับ BCStkIssue บันทึกเอกสารให้ =1, แก้ไข=2, ลบเอกสารให้=3
		        		        	
		        	session.save(bean);
		        	
		    		System.out.println(CommonUtils.getDateTimeForStamp()+" #use :ProcessStockBean #update "+
		  				  "#item:"+iSub.getItemCode());
//			 				BCWinService จะ update: lastBuyPrice ให้กับ item ที่ซื้อเข้ามา แบบ auto อยู่แล้ว	
//				    		new BCItemDAO().updateLastBuyPrice(sub.getItemCode(),sub.getPrice());			    		
				}					
			}         
//start#######################                            	    		    			
    		
    		if(invoice.getDocNo().substring(0, 3).equalsIgnoreCase("BCS")) {
    			//บันทึกเงินสดย่อย
    			BCPayMoneyBean pay = (BCPayMoneyBean)BCPayMoneyBO.getObject(form);			
    			//BCPayMoneyDAO dao4 = new BCPayMoneyDAO();
    			session.save(pay);
    			
    			// ถ้ามูลค่าสุทธิ < ยอดรวมสินค้า   ->ถือเป็นหัก ณ ที่จ่าย			
    			if(invoice.getNetDebtAmount().doubleValue()<invoice.getTotalAmount().doubleValue()) {
    				BCAPWTaxListBean wTax = (BCAPWTaxListBean)BCAPWTaxListBO.getObject(form);
    				//BCAPWTaxListDAO dao6 = new BCAPWTaxListDAO();
    				session.save(wTax);
    				
    				invoice.setSumOfWTax(wTax.getwTaxAmount());
    			}			
    		}else if(invoice.getDocNo().substring(0, 3).equalsIgnoreCase("CRS") ||
    				invoice.getDocNo().substring(0, 3).equalsIgnoreCase("CMD")) {				
    				//BCAPDAO apDao = new BCAPDAO();
    				//apDao.updateDebtAmount(invoice);
    						    					    				
    	            q = session.createQuery("FROM BCAPBean WHERE code ='"+invoice.getApCode()+"'");
    	            resultList = q.list();
    	            
    	            if (resultList!=null && resultList.size()>0) {		            
    	            	BCAPBean h = (BCAPBean)resultList.get(0);
    	            	h.setDebtAmount(h.getDebtAmount()+invoice.getNetDebtAmount());		        
    	            	session.update(h);    	            
    	            }
    				//b
    				System.out.println("update :DebtAmount"); 
    		}
    		
    		// ถ้ามีเลขที่ใบกำกับภาษี (taxNo) ให้เพิ่มใน BCInputTax 		
    		if(invoice.getTaxNo()!=null && !invoice.getTaxNo().equals("")) {
    			BCInputTaxBean tax = (BCInputTaxBean)BCInputTaxBO.getObject(form);
    			//BCInputTaxDAO dao5 = new BCInputTaxDAO();
    			session.save(tax);
    		}
    		
    		session.save(invoice);    		
    		//dao.addITTL_P2(invoice);
    		
    		//start add to p2
			String hql = "FROM BCPurchaseOrderBean WHERE docNo in (";
			Iterator it2 = invoice.getBcAPInvoiceSubs().iterator();
			while (it2.hasNext()) {
				BCAPInvoiceSubBean isub = (BCAPInvoiceSubBean) it2.next();
				hql +="'"+isub.getPoRefNo()+"',";
			}
			hql = hql.substring(0, hql.length()-1)+") order by docNo";
			
            q = session.createQuery(hql);
            List poList = q.list();				
			
			ArrayList<ITTL_P2> al = new BCAPInvoiceBO().getP2List(invoice);
			al = new BCAPInvoiceBO().getP2SetPODetails(poList, al);
			al = new BCAPInvoiceBO().getP2ProjectCode(invoice, al);
						
			q = session.createQuery("FROM BCAPBean WHERE code = '"+invoice.getApCode()+"'");
			
			if(q.list().size()>0) {
				BCAPBean ap = (BCAPBean)q.list().get(0);
				for (int i = 0; i < al.size(); i++) {
					ITTL_P2 p2 = (ITTL_P2)al.get(i);
					p2.setApName(ap.getName1());
				}
			}
			hql = "FROM BCItemUnitBean ";
			q = session.createQuery(hql);
			
			if(q.list().size()>0) {
				for (int i = 0; i < al.size(); i++) {
					ITTL_P2 p2 = (ITTL_P2)al.get(i);
					for (int u = 0; u < q.list().size(); u++) {
						BCItemUnitBean unit = (BCItemUnitBean)(q.list().get(u));
						if(p2.getUnitCode().equalsIgnoreCase(unit.getCode())) {
							p2.setUnitName(unit.getName());
						}
					}
				}
			}
			
			for (int i = 0; i < al.size(); i++) {
				ITTL_P2 p2 = (ITTL_P2)al.get(i);
				session.save(p2);
			}
    		//end add to p2
//end#######################            
			session.getTransaction().commit();						
			
    		System.out.println(CommonUtils.getDateTimeForStamp()+" Database Commit");              				
			return true;
		}catch(Exception e) {
			System.out.println(e.toString());
			System.out.println(CommonUtils.getDateTimeForStamp()+" Database Not Commit");			
			return false;
		}finally {
			session.close();
		}
	}

	@SuppressWarnings("rawtypes")
	public boolean addInvoice(ActionForm form) {
		Session session = null;

		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();		
			
			BCAPInvoiceBean  inv = (BCAPInvoiceBean)BCAPInvoiceBO.getObject(form);
						
			org.hibernate.Query q = session.createQuery("FROM BCAPInvoiceBean WHERE docNo = '"+inv.getDocNo()+"' ");
			List al = q.list();	
			if(al!=null && al.size()>0) {
				String docNoStr = BCAPInvoiceBO.getPreDocNo(inv.getDocNo(), inv.getDocDate());
				q = session.createQuery("SELECT MAX(docNo) FROM BCAPInvoiceBean WHERE docNo like '"+docNoStr+"%' ");	
            } 			
			
			
			
    		System.out.println(CommonUtils.getDateTimeForStamp()+" Database Commit");              				
			return true;
		}catch(Exception e) {
			System.out.println(e.toString());
			System.out.println(CommonUtils.getDateTimeForStamp()+" Database Not Commit");			
			return false;
		}finally {
			session.close();
		}
	}

	public boolean add(ActionForm form, HttpServletRequest request,
			HttpServletResponse response) {
		Session session = null;

		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();		
			
			BCAPInvoiceBean  inv = (BCAPInvoiceBean)BCAPInvoiceBO.getObject(form);
						
			org.hibernate.Query q = session.createQuery("FROM BCAPInvoiceBean WHERE docNo = '"+inv.getDocNo()+"' ");
			List al = q.list();	
			if(al!=null && al.size()>0) {
				String maxDocNo = CommonUtils.getMaxBCAPInvoiceDocNo(inv.getDocNo());
				inv.setDocNo(maxDocNo);		
            } 			
			
			if(inv.getDocNo().substring(0, 3).equalsIgnoreCase("BCS")) {
				//บันทึกเงินสดย่อย
				BCPayMoneyBean pay = (BCPayMoneyBean)BCPayMoneyBO.getObject(form);			
				BCPayMoneyDAO dao4 = new BCPayMoneyDAO();
//				dao4.add(pay);
				session.save(pay);
				// ถ้ามูลค่าสุทธิ < ยอดรวมสินค้า   ->ถือเป็นหัก ณ ที่จ่าย			
				if(inv.getNetDebtAmount().doubleValue()<inv.getTotalAmount().doubleValue()) {
					BCAPWTaxListBean wTax = (BCAPWTaxListBean)BCAPWTaxListBO.getObject(form);
					BCAPWTaxListDAO dao6 = new BCAPWTaxListDAO();
//					dao6.add(wTax);
					session.save(wTax);
					
					inv.setSumOfWTax(wTax.getwTaxAmount());
				}			
			}else if(inv.getDocNo().substring(0, 3).equalsIgnoreCase("CRS") ||
						inv.getDocNo().substring(0, 3).equalsIgnoreCase("CMD")) {				
//					BCAPDAO apDao = new BCAPDAO();
//					apDao.updateDebtAmount(inv);
					//start
					double tempAmount = 0;
					//เผื่อกรณีแก้ไข docNo อันนั้น คือให้ดึงมูลค่าใน invoice ใบนั้นออกมาก่อน แล้วไปหักออกจาก ยอดหนี้เดิม แล้วค่อยบวก ยอด invoiceใหม่เข้าไป
					// ปล. ต้องแก้ไขยอดหนี้ให้เรียบร้อยก่อน   ก่อนที่จะทำการ update invoice ที่แก้ไข (ควรให้การแก้ไข invoice ถูก save เป็นลำดับสุดท้าย)
					String hql ="FROM BCAPInvoiceBean WHERE docNo ='"+inv.getDocNo()+"'";
		            org.hibernate.Query q2 = session.createQuery(hql);
		            List resultList_1 = q2.list();			
		            if (resultList_1!=null && resultList_1.size()>0) {		            
		            	BCAPInvoiceBean h = (BCAPInvoiceBean)resultList_1.get(0);
		            	tempAmount = h.getNetDebtAmount();
		            }			
								
					hql ="FROM BCAPBean WHERE code ='"+inv.getApCode()+"'";
		            q = session.createQuery(hql);
		            resultList_1 = q.list();
		            
		            if (resultList_1!=null && resultList_1.size()>0) {		            
		            	BCAPBean h = (BCAPBean)resultList_1.get(0);
		            	h.setDebtAmount(h.getDebtAmount()-tempAmount+inv.getNetDebtAmount());		        
		            	session.update(h);		            	
		            }
					//end
					System.out.println("update :DebtAmount"); 
			}
			// ถ้ามีเลขที่ใบกำกับภาษี (taxNo) ให้เพิ่มใน BCInputTax 		
			if(inv.getTaxNo()!=null && !inv.getTaxNo().equals("")) {
				BCInputTaxBean tax = (BCInputTaxBean)BCInputTaxBO.getObject(form);
				BCInputTaxDAO dao5 = new BCInputTaxDAO();
//				dao5.add(tax);
				session.save(tax);
			}		
//			dao.add(inv);
			session.save(inv);
			//start addP2
//			dao.addITTL_P2(inv);
			String hql = "FROM BCPurchaseOrderBean WHERE docNo in (";
			Iterator it = inv.getBcAPInvoiceSubs().iterator();
			while (it.hasNext()) {
				BCAPInvoiceSubBean isub = (BCAPInvoiceSubBean) it.next();
				hql +="'"+isub.getPoRefNo()+"',";
			}
			hql = hql.substring(0, hql.length()-1)+") order by docNo";
			
            q = session.createQuery(hql);
            List poList = q.list();				
			
			ArrayList al_p2 = new BCAPInvoiceBO().getP2List(inv);
			al_p2 = new BCAPInvoiceBO().getP2SetPODetails(poList, al_p2);
			al_p2 = new BCAPInvoiceBO().getP2ProjectCode(inv, al_p2);
			
			hql = "FROM BCAPBean WHERE code = '"+inv.getApCode()+"'";
			q = session.createQuery(hql);
			
			if(q.list().size()>0) {
				BCAPBean ap = (BCAPBean)q.list().get(0);
				for (int i = 0; i < al_p2.size(); i++) {
					ITTL_P2 p2 = (ITTL_P2)al_p2.get(i);
					p2.setApName(ap.getName1());
				}
			}
			
			hql = "FROM BCItemUnitBean ";
			q = session.createQuery(hql);
			
			if(q.list().size()>0) {
				for (int i = 0; i < al_p2.size(); i++) {
					ITTL_P2 p2 = (ITTL_P2)al_p2.get(i);
					for (int u = 0; u < q.list().size(); u++) {
						BCItemUnitBean unit = (BCItemUnitBean)(q.list().get(u));
						if(p2.getUnitCode().equalsIgnoreCase(unit.getCode())) {
							p2.setUnitName(unit.getName());
						}
					}
				}
			}			
			
			
			for (int i = 0; i < al_p2.size(); i++) {
				ITTL_P2 p2 = (ITTL_P2)al_p2.get(i);
				session.save(p2);
			}
			
			//end addP2
			
			// ใส่ข้อมูล itemCode รายการนั้นใน table: processStock 
			ProcessStockDAO p = new ProcessStockDAO();
			String[] item_of_service = {"A-01", "A-02", "C-3",  "C-03",  "D-03", "D-04", "D-4",  "E-04", "E-05", "E-4",  "E-5",  "E-S-E","EXPORT",
										"F-04", "F-05", "F-06", "F-4",  "F-5",  "F-6",  "G-06", "G-08", "G-10", "G-11", "G-12", 
										"G-13", "G-14", "G-24", "GE",   "H-07", "H-08", "H-15", "HO",   "I-10", "I-16", "IM",
										"INT",  "J-11", "J-17", "K-14", "K-15", "K-16", "K-17", "K-18", "L-18", "L-19", "M-19",
										"M-21", "N-20", "N-22", "O-21", "P-22", "P-23", "Q-23", "R-24", "R-25", "S-25", "SE", "TREEX", "TRECO", 
										"SER-006","SES","ST",   "T-26", "U-27", "V-28", "Z-01", "Z-02", "Z-03", "Z-04", "Z-05", "Z-06"};
			
			Iterator it_inv = inv.getBcAPInvoiceSubs().iterator();
			
			while(it_inv.hasNext()) {
				BCAPInvoiceSubBean sub = (BCAPInvoiceSubBean)it_inv.next();
										
				BCStkRequestDAO dao2 = new BCStkRequestDAO();
 				
				//start
				//update ว่าใบ พ.1 ขอซื้อนั้น ใช้บันทึกซื้อแล้ว (แม้ไม่ได้ซึ้อทุก item )
//				dao2.updateStatus(sub.getStkReqNo(),"2"); //0=รอเบิก, 1=รอซื้อสร้าง po แล้ว, 2=ซื้อแล้ว, 3=จ่ายแล้ว, 4=ยืนยันรับของ
				hql ="FROM BCStkRequestBean WHERE docNo ='"+sub.getStkReqNo()+"'";
	            q = session.createQuery(hql);
	            List resultList_2 = q.list();			

	            for(int i=0; i<resultList_2.size(); i++ ) {
	            	BCStkRequestBean h = (BCStkRequestBean)resultList_2.get(i);            	
	            	h.setIttl_status("2");
			    	
	            	Iterator it_2 = h.getBcStkRequestSubs().iterator();
	            	while (it_2.hasNext()) {
						BCStkRequestSubBean sub_2 = (BCStkRequestSubBean) it_2.next();
						sub_2.setIttl_status("2");
					}
	            	
	                session.update(h);
	            }   					
				
				//end
				
	            //start
				//update item ที่ซื้อเข้ามาตาม rowOrder
//				dao2.updateStatusAfterCreateInvoice(form,"2");
				BCAPInvoice2Form invForm = (BCAPInvoice2Form)form;
				
				if(invForm.getIttl_rowOrderOfStkReq()!=null && invForm.getIttl_rowOrderOfStkReq().length>0) {
				
					String rowOrder = "";
					for (int i = 0; i < invForm.getIttl_rowOrderOfStkReq().length; i++) {
						
						if(invForm.getIttl_rowOrderOfStkReq()[i]!=null) {
							rowOrder = rowOrder +""+ invForm.getIttl_rowOrderOfStkReq()[i] + ",";
						}											
					}	
					
					if(rowOrder.length()>0) {
						rowOrder = rowOrder.substring(0, rowOrder.length()-1);
					}else {
						rowOrder = "0";
					}
					
					hql = "FROM BCStkRequestSubShowBean s  " +
			 		"WHERE  s.rowOrder in ( "+rowOrder+" )  ";					 
									
		            q = session.createQuery(hql);
		            List resultList_s = q.list();			

		            for(int i=0; i<resultList_s.size(); i++ ) {
		            	BCStkRequestSubShowBean h = (BCStkRequestSubShowBean)resultList_s.get(i);
		            	
		    			for (int x = 0; x < invForm.getIttl_rowOrderOfStkReq().length; x++) {	    				
		    				if(invForm.getIttl_rowOrderOfStkReq()[x]!=null && invForm.getIttl_rowOrderOfStkReq()[x].equalsIgnoreCase(""+h.getRowOrder())) {
		    					h.setPrice(Float.valueOf(invForm.getPrice()[x]));
		    					h.setAmount(h.getPrice()*h.getQty());
		    				}											
		    			}
		            	
		            	h.setIttl_status("2");            	    			        
		            	
		                session.update(h);
		            }                                 		 
				} 
				//end
				
				//start
//				BCPurchaseOrderDAO dao4 = new BCPurchaseOrderDAO();
//				dao4.updateStatusAfterSaveInvoice(sub.getPoRefNo());// หลังจาก save invoice จะต้อง update : poStatus=2, billStatus=1
				hql = "FROM BCPurchaseOrderBean h where h.docNo = '"+sub.getPoRefNo()+"'";
		            
		        q = session.createQuery(hql);
		        List resultList_p = q.list();			

	            for(int i=0; i<resultList_p.size(); i++){
	            	BCPurchaseOrderBean h = (BCPurchaseOrderBean)resultList_p.get(i);
	            	h.setPoStatus(2);
	            	h.setBillStatus(1);
	                session.save(h);
	            }       
				//end
				
				boolean isItemService = false;
				for (int i = 0; i < item_of_service.length; i++) {
					if(item_of_service[i].contains(sub.getItemCode())) {
						isItemService = true;
					}				
				}
				if(isItemService==false) {
		        	ProcessStockBean bean = new ProcessStockBean();
		        	bean.setItemCode(sub.getItemCode());
		        	bean.setDocDate(sub.getDocDate());
		        	bean.setProcessFlag(0);
		        	bean.setFlowStatus(1);
		        	bean.setProcessType(5);
		        	bean.setProcessCase(1);// สำหรับ BCStkIssue บันทึกเอกสารให้ =1, ลบเอกสารให้=3
		        		        	
		        	p.add(bean);
		        	session.save(bean);
				}
				//###########
			}
			
			
			
    		System.out.println(CommonUtils.getDateTimeForStamp()+" Database Commit");              				
			return true;
		}catch(Exception e) {
			System.out.println(e.toString());
			System.out.println(CommonUtils.getDateTimeForStamp()+" Database Not Commit");			
			return false;
		}finally {
			session.close();
		}
	}	
}
